/*
* @desc
*  材料分会数据录入
*
* 数据库信息
*
*
* 1.读取信息
* 2.获取一个json
* 3.导入到分类的表中
* */
//插入父类的 sql
// INSERT INTO `match_deleizi_214`.`tb_dict_detail`(`id`, `pid`, `dict_id`, `projectId`, `chinese`, `english`, `order_by`, `create_time`) VALUES ('cacc2ca3-baa0-4275-9b49-0fb0f08b0d52', '0', 'e7c658fc-eb30-4a22-bb0c-39aea604066f', '3f8ad648-c7ca-486c-9ec5-c562cc3d13e0', '夹芯材料（泡沫 & 多孔材料 & 轻木）', 'types1', 0, '2020-11-27 10:43:31');
//修改父类的sql
// UPDATE `match_deleizi_214`.`tb_dict_detail` SET `pid` = '0', `dict_id` = 'e7c658fc-eb30-4a22-bb0c-39aea604066f', `projectId` = '3f8ad648-c7ca-486c-9ec5-c562cc3d13e0', `chinese` = '夹芯材料（泡沫 & 多孔材料 & 轻木）', `english` = 'types1', `order_by` = 0, `create_time` = '2020-11-27 10:43:31' WHERE `id` = 'cacc2ca3-baa0-4275-9b49-0fb0f08b0d52';
//
/*
* @数据库信息
*   datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://47.95.80.142:3306/match_deleizi_214?useUnicode=true&characterEncoding=UTF8&connectTimeout=10000&socketTimeout=100000&allowMultiQueries=true&useSSL=false
    username: root
    password: w#3#6@x$eQ%F30j(2pt7)xk@sx$g9x
* */

const fs = require('fs')
const path = require('path')
const ExcelJS = require('exceljs');
const lodash = require('lodash')
const mysql = require('mysql2/promise');

async function getMysql2(host, database, password, user, port) {
    if (!host) {
        host = this.ctx.session.currentUser.host;
        database = this.ctx.session.currentUser.database;
        password = this.ctx.session.currentUser.password;
        user = this.ctx.session.currentUser.user;
        port = this.ctx.session.currentUser.port;
    }
    const connection = await mysql.createPool({
        host, database, password, user, port,
        waitForConnections: true,
        connectionLimit: 10,
        queueLimit: 0
    })
    return connection
}

function uuid() {
    return 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function (c) {
        var r = Math.random() * 16 | 0,
            v = c == 'x' ? r : (r & 0x3 | 0x8);
        return v.toString(16);
    });
}


async function init() {
    const workbook = new ExcelJS.Workbook();
    await workbook.xlsx.readFile("./dist/关键词检索（顾）(1).xlsx");
    const worksheet = workbook.getWorksheet('展商分类');
    const rows = worksheet.getRows(0, 327)
    const myJson = [];
    rows.map(row => {
        // console.log('row', row.values)
        if (row.values.length > 0) {
            if (row.values[1] != '序号') {
                var one = {
                    序号: row.values[1],
                    大类: row.values[2],
                    分类: row.values[3],
                    展商名称: row.values[4],
                    展位号: row.values[5],
                    展品: row.values[6]
                }
                myJson.push(one)
            }
        }
    })

    //子类去重
    var parentAndchildMap={}
    lodash.map(myJson,item=>{
        if(!parentAndchildMap[item.大类]){
            parentAndchildMap[item.大类]=[]
        }
        if(item.分类){
            parentAndchildMap[item.大类].push(item.分类.trim&&item.分类.trim())
        }
    })
    lodash.map(parentAndchildMap,(values,key)=>{
        var _value=lodash.uniq(values)
        parentAndchildMap[key]=_value
    })
    //查询

    // const [rows] = await mysql2.execute(sql);
    // var values=Object.keys({'夹芯材料（泡沫 & 多孔材料 & 轻木）':'夹芯材料（泡沫 & 多孔材料 & 轻木）'})
    var values=Object.keys(parentAndchildMap)
    for (let i = 0; i < values.length; i++) {
        var parentName=values[i]
        var childrenNames = parentAndchildMap[parentName]
        //插入子类
        var [findParent]=await mysql2.execute(`select * from  tb_dict_detail where chinese='${parentName}' `)
        //找到父类
        if(findParent&&findParent[0]&&findParent[0].id){
            var parent=findParent[0];
            for (let j = 0; j <childrenNames.length ; j++) {
                var childName=childrenNames[j]
                var sql2= `select * from  tb_dict_detail where chinese='${childName}' and pid='${parent.id}' `
                var [childResult] = await mysql2.execute(sql2)
                var updateChildSql=``
                if(childResult&&childResult[0]&&childResult[0].id){
                    //没有孩子 就 insert
                    if(childResult&&childResult[0]&&childResult[0].id){
                        updateChildSql = `UPDATE tb_dict_detail  SET chinese = '${childName}' WHERE 'id' = '${childResult[0].id}';`
                    }
                }else{
                    //存在孩子 就update
                    if(childName){
                        updateChildSql = `INSERT INTO tb_dict_detail (id,pid, dict_id, projectId, chinese,english, order_by, create_time) 
      VALUES ('${uuid()}','${parent.id}', 'e7c658fc-eb30-4a22-bb0c-39aea604066f', '3f8ad648-c7ca-486c-9ec5-c562cc3d13e0', '${childName}', '', 0, '2020-11-27 10:43:31');`
                    }

                }

                if(updateChildSql){
                    console.log("updateChildSql", updateChildSql)
                    await mysql2.execute(updateChildSql)
                }
            }
        }
    }


    console.log("导入结束")

}

init();



